-- To speed up the load of errros in errhande-loop, we divide the orders
-- into chunks. If a chunk has an error, we revidide the chunk into
-- smaller ones, and try again. Eventually we will have a chunk size 
-- of 1, but only for a smaller amount of orders.
SET NOCOUNT ON
USE BigDB
go
DECLARE @starttime datetime2(3) = sysdatetime(),
        @afterms   int,
        @chunkno   int,
        @rowc      int = 0,
        @totalrowc int = 0,
        @oldrowc   int = 0

DECLARE @chunksizes TABLE (size int NOT NULL PRIMARY KEY)

INSERT @chunksizes (size)
   VALUES(2000), (100), (10)

-- We create a temp table with the orders to work with 
-- divided into chunks.
CREATE TABLE #tmporders 
             (OrderID  int NOT NULL,
              ChunkNo  int NOT NULL,
              INDEX clu_ix UNIQUE CLUSTERED (ChunkNo, OrderID))

INSERT #tmporders(OrderID, ChunkNo)
   SELECT OrderID, row_number() OVER(ORDER BY OrderID) / 
                                     (SELECT MAX(size) FROM @chunksizes)
   FROM   dbo.NewOrders

-- Chunks are dynamic, so we cannot run a static cursor,
-- but we handle the loop ourselves.
WHILE EXISTS (SELECT * FROM #tmporders)
BEGIN TRY
   -- Get next chunk.
   SELECT @chunkno = MIN(ChunkNo) FROM #tmporders

   -- Try to import this chunk.
   BEGIN TRANSACTION

   INSERT BigOrders(OrderID, CustomerID, EmployeeID, OrderDate, 
                    RequiredDate, ShipVia, Freight, ShipName, 
                    ShipAddress, ShipCity, ShipRegion, ShipPostalCode, 
                    ShipCountry, Discount, 
                    TotalAmount, Status)
     SELECT O.OrderID, O.CustomerID, O.EmployeeID, O.OrderDate, 
            O.RequiredDate, O.ShipVia, O.Freight, O.ShipName, 
            O.ShipAddress, O.ShipCity, O.ShipRegion, O.ShipPostalCode, 
            O.ShipCountry, O.Discount, 
            D.Amount * (1 - O.Discount) + O.Freight, 'N'
     FROM   dbo.NewOrders  O
     JOIN   (SELECT OrderID, Amount = SUM(isnull(Quantity * UnitPrice, 0))
             FROM   dbo.NewDetails
             GROUP  BY OrderID) AS D ON O.OrderID = D.OrderID
     WHERE  EXISTS (SELECT *
                    FROM   #tmporders t
                    WHERE  t.OrderID = O.OrderID
                      AND  t.ChunkNo = @chunkno) 
     SELECT @rowc = @@rowcount

   INSERT BigDetails (OrderID, ProductID, Quantity, UnitPrice)
      SELECT D.OrderID, D.ProductID, D.Quantity, D.UnitPrice
      FROM   dbo.NewDetails D
      WHERE  EXISTS (SELECT *
                     FROM   #tmporders t
                     WHERE  t.OrderID = D.OrderID
                       AND  t.ChunkNo = @chunkno) 
   COMMIT TRANSACTION

   -- Diagnostics
   SELECT @totalrowc += @rowc, @rowc = 0
   IF @totalrowc - @oldrowc >= 1000
   BEGIN
      SELECT @afterms = datediff(ms, @starttime, sysdatetime())
      RAISERROR('%d orders in chunk %d inserted in %d ms.', 0, 1, 
                @totalrowc, @chunkno, @afterms) WITH NOWAIT
      SELECT @oldrowc = @totalrowc
   END

   -- Delete this chunk from the work table.
   DELETE #tmporders WHERE ChunkNo = @chunkno
END TRY
BEGIN CATCH
   -- This chunk failed. Starting with rolling back any
   -- open transaction.
   IF @@trancount > 0 ROLLBACK TRANSACTION

   -- Get current size of chunk.
   DECLARE @chunksize    int,
           @newchunksize int

   SELECT @chunksize = COUNT(*) 
   FROM   #tmporders
   WHERE  ChunkNo = @chunkno

   -- As long as chunk size is > 1, divide into smaller for retry.
   IF @chunksize > 1
   BEGIN 
      -- Get the next chunksize from the table.
      SELECT @newchunksize = 
          isnull((SELECT MAX(size) FROM @chunksizes 
                  WHERE size < @chunksize),1)

      -- Produce diagnostic message.
      RAISERROR('Redividing chunk %d from chunksize %d to new size %d', 0, 1,
                 @chunkno, @chunksize, @newchunksize) WITH NOWAIT

      -- Assign new chunk numbers for the orders in the current chunk.
      ; WITH ChunkCTE AS (
         SELECT ChunkNo,
                TmpChunk = row_number() OVER(ORDER BY OrderID) /
                           @newchunksize 
         FROM   #tmporders
         WHERE  ChunkNo = @chunkno
      )
      UPDATE ChunkCTE
      SET    ChunkNo = TmpChunk + (SELECT MAX(ChunkNo) FROM #tmporders)
   END
   ELSE
   BEGIN
      DECLARE @errmsg  nvarchar(2048) = error_message(),
              @orderid int

      SELECT @orderid = OrderID 
      FROM   #tmporders 
      WHERE  ChunkNo = @chunkno

      -- As with the loop, in a real-world case you would log the error
      -- to a table, but not raise an error to prevent a caller from
      -- intercepting the loop with a TRY block.
      RAISERROR('Inserting order %d failed with error: %s', 16, 1, 
                 @orderid, @errmsg) WITH NOWAIT

      -- Delete this chunk, so we don't retry.
      DELETE #tmporders WHERE ChunkNo = @chunkno
   END
END CATCH

SELECT @afterms = datediff(ms, @starttime, sysdatetime())
RAISERROR('All %d rows processed in %d ms.', 0, 1, @totalrowc, @afterms) WITH NOWAIT
go
DROP TABLE #tmporders
-- Delete the inserted orders, so that we can re-test.
DELETE BigDetails 
   WHERE OrderID IN (SELECT OrderID FROM NewOrders)
DELETE BigOrders
   WHERE OrderID IN (SELECT OrderID FROM NewOrders)
go

